package com.lmm.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import javax.naming.Context;
import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.wrappers.StringTrimmedResultSet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 连接数据库类（采用JDBC）
 * 
 * @author leiming
 * 
 * 
 */
public class DBCon {

	private static final Logger log = LoggerFactory.getLogger(DBCon.class);

	@SuppressWarnings("unused")
	private static Context initCtx;
	@SuppressWarnings("unused")
	private static Context ctx;
	private static DataSource ds;

	public static QueryRunner QUERY_RUN;// db util 数据库操作对象

	//
	private ResultSet rs = null;
	private Statement stmt = null;
	private CallableStatement cstmt = null;
	private Connection con = null;

	// 初始化连接池信息
	static {
		try {

			/***
			 * //tomcat 池 initCtx = new InitialContext(); ctx = new
			 * InitialContext();
			 * 
			 * ctx = (Context) initCtx.lookup("java:comp/env"); String name =
			 * Config.RESOURE_NAME;//连接名 if(name ==null && "".equals(name)) name
			 * = "jdbc/dbcon";//默认连接对象 // 获取连接池对象 Object obj = (Object)
			 * ctx.lookup(name); // 类型转换 ds = (javax.sql.DataSource) obj;
			 */
			ds = PoolDB.setupDataSource("jdbc/sddb");

			QUERY_RUN = new QueryRunner(ds) {
				private Logger log = LoggerFactory.getLogger(this.getClass());

				@Override
				public <T> T query(Connection conn, String sql,
						ResultSetHandler<T> rsh, Object... params)
						throws SQLException {
					log.debug("sql:" + sql);
					// System.out.println("sql:"+sql);
					return super.query(conn, sql, rsh, params);
				}

				/**
				 * 去掉空格
				 */
				@Override
				protected ResultSet wrap(ResultSet rs) {
					return StringTrimmedResultSet.wrap(rs);
				}

			};
		} catch (Exception e) {
			log.error("数据库连接池初始化失败。请检查数据配置。");
			throw new RuntimeException(e.getCause());
		}
	}

	public static DataSource getDataSource() {
		return ds;
	}

	public static Connection getConnection() {
		try {

			Connection conn = ds.getConnection();
			log.debug("从连接池中获得一个连接对象：" + conn.toString());
			return conn;
		} catch (SQLException e) {
			log.error("得数据库连接错误,错误原因：" + e.getMessage());
			throw new RuntimeException(e.getCause());
		}
	}

	private void createConnection() {

		try {
			if (this.con == null || this.con.isClosed()) {
				this.con = getConnection();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行查询操作
	 * 
	 * @param sql语句
	 * @return 返回记录集
	 */
	public ResultSet executeQuery(String sql) {
		stmt = null;
		rs = null;

		try {
			createConnection();
			if (this.con != null) {
				stmt = this.con.createStatement();
				rs = stmt.executeQuery(sql);
			}
			return rs;
		} catch (Exception e) {
			log.error("executeQuery(String sql)出错:" + e.getMessage());
			log.error("sql= " + sql);

			return rs;
		}

	}

	/**
	 * 执行查询操作(可设置Statement参数)
	 * 
	 * @param sql语句
	 * @return 返回记录集
	 */
	public ResultSet executeQuery(String sql, int id, int id2) {
		try {
			stmt = null;
			rs = null;
			createConnection();
			if ((id == 1) && (id2 == 1))
				stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
						ResultSet.CONCUR_READ_ONLY);
			if ((id == 1) && (id2 == 2))
				stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
						ResultSet.CONCUR_UPDATABLE);
			if ((id == 2) && (id2 == 1))
				stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			if ((id == 2) && (id2 == 2))
				stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
			if ((id == 3) && (id2 == 1))
				stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			if ((id == 3) && (id2 == 2))
				stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery(sql);

		} catch (Exception e) {
			System.out.println("executeQuery(String sql, int id, int id2)出错:"
					+ e.getMessage());
			System.out.println("sql= " + sql);
		}
		return rs;
	}

	/**
	 * 执行增加、删除 操作
	 * 
	 * @param sql语句
	 */
	public boolean executeSql(String sql) {
		boolean bSuccess = false;
		try {
			stmt = null;
			createConnection();
			if (this.con != null) {
				stmt = this.con.createStatement();
				bSuccess = stmt.execute(sql);
			}
		} catch (Exception e) {
			System.out.println("executeSql(String sql)出错：" + e.getMessage());
			System.out.println("sql= " + sql);
			return bSuccess;
		} finally {
			this.close();
		}
		return bSuccess;
	}

	/**
	 * 执行存储过程（只带一个输入参数形式，且只输出一个参数的）----得到存储过程执行后的值
	 * 
	 * @param pdName
	 *            (存储过程调用名。如："{call SP_VIEWUSER(?)}")
	 * @param inParam
	 *            输入参数
	 * @param inType
	 *            (输入参数对应的类型(即存储过程输入参数的数据类型)，参数通过Types对象的属性传入)
	 * @param outTypes
	 *            (输出参数对应的类型(即存储过程输出参数的数据类型)，参数通过Types对象的属性传入)
	 * @return Object
	 * @throws SQLException
	 */
	public Object executeProcedure(String pdName, String inParam, int inType,
			int outTypes) throws SQLException {
		if ("".equals(pdName))
			return null;
		Object obj = null;
		cstmt = null;
		try {
			createConnection();

			cstmt = this.con.prepareCall(pdName);
			if (inType == Types.VARCHAR || inType == Types.CHAR)
				cstmt.setString(1, inParam);
			else if (inType == Types.INTEGER)
				cstmt.setInt(1, Integer.valueOf(inParam));
			else if (inType == Types.DOUBLE)
				cstmt.setDouble(1, Double.valueOf(inParam));
			else if (inType == Types.FLOAT)
				cstmt.setFloat(1, Float.valueOf(inParam));

			cstmt.registerOutParameter(2, outTypes);

			cstmt.execute();
			obj = cstmt.getObject(2);

		} catch (Exception e) {
			System.out.println("CConnectionDB.executeProcedure出错："
					+ e.getMessage());
			System.out.println("pdName= " + pdName + "   inParam= " + inParam
					+ "   inType= " + inType + "   outTypes= " + outTypes);
			return null;
		} finally {
			this.close();
		}
		return obj;
	}

	/**
	 * 支持多个参数，多个输出参数，多个输入参数
	 * 
	 * @param procName
	 * @param inParam
	 * @param intType
	 * @param outType
	 * @return
	 * @throws Exception
	 *             add by YongDongliang
	 */
	public Object[] excuteProc(String procName, String[] inParam, int[] inType,
			String[] inParamName, String[] outParam, int[] outType)
			throws Exception {
		if ("".equals(procName)) {
			return null;
		}
		if (inParam.length != inType.length
				|| inParam.length != inParamName.length) {
			throw new Exception("输入参数和输入参数类型长度不一致");
		}
		if (outParam.length != outType.length) {
			throw new Exception("输出参数和输出参数类型长度不一致");
		}
		cstmt = null;
		Object[] objs = new Object[outParam.length];
		try {
			this.createConnection();
			cstmt = this.con.prepareCall(procName);
			for (int i = 0; i < inParam.length; i++) {
				if (inType[i] == Types.VARCHAR || inType[i] == Types.CHAR)
					cstmt.setString(inParamName[i], inParam[i]);
				else if (inType[i] == Types.INTEGER)
					cstmt.setInt(inParamName[i], Integer.valueOf(inParam[i]));
				else if (inType[i] == Types.DOUBLE)
					cstmt.setDouble(inParamName[i], Double.valueOf(inParam[i]));
				else if (inType[i] == Types.FLOAT)
					cstmt.setFloat(inParamName[i], Float.valueOf(inParam[i]));
			}
			for (int i = 0; i < outParam.length; i++) {
				cstmt.registerOutParameter(outParam[i], outType[i]);
			}
			cstmt.execute();
			for (int i = 0; i < outParam.length; i++) {
				objs[i] = cstmt.getObject(outParam[i]);
			}
		} finally {

			this.close();
		}
		return objs;
	}

	/**
	 * 编译存存存过程
	 * 
	 * @param procName
	 */
	public CallableStatement preperProc(String procName) {
		try {
			this.createConnection();// 创建连接
			cstmt = this.con.prepareCall(procName);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cstmt;
	}

	/**
	 * 释放资源
	 * 
	 * @throws SQLException
	 * 
	 */
	public void close() {

		try {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
			} finally {
				try {
					if (stmt != null) {
						stmt.close();
						stmt = null;
					}
				} finally {
					try {
						if (cstmt != null) {
							cstmt.close();
							cstmt = null;
						}
					} finally {
						if (con != null) {
							if (con.isClosed()) {
								con = null;
							} else {
								con.close();
								con = null;
							}
						}
					}

				}
			}
		} catch (SQLException e) {
			log.error("关闭数据库连接时出错，错误原因：" + e.getMessage());
			e.printStackTrace();
		}
	}
}
